#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";

my $station=$ARGV[0];
my $datatype=$ARGV[1];
my $sort=$ARGV[2];
my $beginDate=$ARGV[3];
my $endDate=$ARGV[4];

if($endDate){$endDate="$endDate 23:59:59";}
my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my @stations=split("\,",$station);
my @datatypes=split("\,",$datatype);
my @sort=split("-",$sort);

my $sortstring ="order by ".join(" ",@sort); 
   $sortstring=$sortstring. ",m_z";

my ($sqlsta,$sth1,$sql,$sth,@array1);

#my $dbname="/var/www/html/RCOOS/website/latest.db";

#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
             #       { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}
 my $dbh=getdbConn();
 if($dbh==0){ die "Cannot connect to database!\n";}

my $stationstring="";
 for(my $i=0;$i<@stations;$i++){
    if($i>0){$stationstring=$stationstring." or "}
    $stationstring=$stationstring."short_name='$stations[$i]'";
 }

  
 # get basic info about the stations
 $sqlsta="select short_name,platform_handle,fixed_longitude,fixed_latitude,description,long_name,begin_date,end_date from platform where $stationstring";
#print "$sqlsta\n";
 print "<table cellpadding=1 width=80% cellspacing=1><tr bgcolor=#ffffdd><td>Station</td><td>Longitude</td><td>Latitude</td><td>Description</td><td>Start Operating</td><td>End Date</td></tr>";
 $sth1 = $dbh->prepare($sqlsta);
 $sth1->execute();
 $stationstring="";
 while (my @row = $sth1->fetchrow_array) {
     print "<tr bgcolor=#eeeeee><td>&nbsp;$row[0]</td><td>&nbsp;$row[2]</td><td>&nbsp;$row[3]</td><td>&nbsp;$row[4]&nbsp;&nbsp;&nbsp;$row[5]</td><td>&nbsp;$row[6]</td><td>&nbsp;$row[7]</td></tr>";
     $stationstring=$stationstring."platform_handle='$row[1]' or ";
 }
 $stationstring="(".substr($stationstring,0,-4).")";
 $sth1->finish();
 print "</table><br><br>";
 
 print "Query Results: <br>Quality Control Flag: 1:bad; 2:suspect; 3:good; -9:missing value; 0: quality not evaluated.<br>";

 for(my $a=0;$a<@datatypes;$a++){
    $sql="select m_type.row_id,obs_type.standard_name,uom_type.standard_name from m_type 
         left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
         left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
         left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
         where obs_type.row_id=$datatypes[$a]";

    $sth = $dbh->prepare($sql);
    $sth->execute();
 #print "\n$sql\n";
    my $m=0;
    while (my @row = $sth->fetchrow_array) {
           
      my $standardname=$row[1];
      my ($uom,$formula)=uom_convert($standardname,$row[2]);

      my $i=0;
  
      my @array1=split(/_/,$standardname);
      $standardname="";
      for($i=0;$i<@array1;$i++){
         $array1[$i]=ucfirst($array1[$i]);
         $standardname=$standardname.$array1[$i]." ";
      }

      print "<pre>";
      

      if($sortstring eq "order by platform_handle"){
          $sortstring="order by platform_handle,m_date";
      }
      $sqlsta="select m_value,qc_level,platform_handle,m_date,m_z from multi_obs where m_date>='$beginDate' and m_date<='$endDate' and $stationstring and m_type_id=$row[0] and m_value is not null $sortstring";
      
      $sth1 = $dbh->prepare($sqlsta);
      $sth1->execute();
      $i=0;
#print "$sqlsta\n";
      
      while (my @row1 = $sth1->fetchrow_array) {
          $row1[0]=value_convert($formula,$row1[0]);
          if($i==0){print "Date time         \tStation      \t$standardname($uom) \t Depth (m) \t QC Flag\n";}    
          my $sus="";
          $i++;
          $row1[3]=substr($row1[3],0,10)." ".substr($row1[3],11,5);
          @array1=split(/\./,$row1[2]);
          if($row1[4]==-99999){$row1[4]="";}
          if($row1[4]==0){$row1[4]="0   ";}
          if(!$row1[4]){$row1[4]="          ";}
          print "$row1[3]\t $array1[1] \t $row1[0] \t$row1[4]\t $row1[1]\n"; 
          $i++;    
      }
      if($i==0){print "No $standardname data found for the specified time period\n";}
      $sth1->finish();
      $m++;
   }

   print "</pre><br>";
   $sth->finish();
 
} # end of a loop 

 undef $sth1;
 undef $sth;
 $dbh->disconnect();


 sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { print "not found: $obs_type $uom \n"; return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 #unit conversion using supplied equation(e.g. celcius to fahrenheit)
 #my $conversion_string = $conversion_formula;
  #  $conversion_string =~ s/var1/$measurement/g;
 #my $conversion_val = eval $conversion_string;

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}


